package com.tjtt.tdm.base;

import java.io.Serializable;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.LongType;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.util.Assert;

import com.tjtt.tdm.exception.DaoException;
import com.tjtt.tdm.exception.SystemException;


@SuppressWarnings("unchecked")
public class HibernateEntityDAO<T> extends HibernateDaoSupport implements EntityDAO<T> {
	private Class<T> entityClass;
	protected Class<T> getEntityClass() {
		return entityClass;
	}
	public void setEntityClass(Class<T> t){
		this.entityClass = t;
	}
	/**
	 * 在构造函数中将泛型T.class赋给entityClass
	 */
	public HibernateEntityDAO() {
		entityClass = GenericsUtils.getGenericClass(getClass());
	}
	
	public void update(Object object) throws DaoException{
		try{
			this.getHibernateTemplate().update(this.getHibernateTemplate().merge(object));
		}catch(Exception e){
			throw new DaoException("修改时出错，请确认操作是否正确",e);
		}
	}
	
	public void bulkUpdate(String hql, Object... object) throws DaoException {
		try{
			this.getHibernateTemplate().bulkUpdate(hql,object);
		}catch(Exception e){
			throw new DaoException("修改时出错，请确认操作是否正确",e);
		}
	}
	/**
	 * 获取对象的方法
	 * @param obj
	 * @param id
	 * @return
	 */
	public T get(Class<Object> obj,Serializable id){
		return (T) getHibernateTemplate().get(obj, id);
	}
	
	public T get(Serializable id) throws DaoException{
		T o = null;
		if(id == null){
			throw new DaoException("传入的参数为空，不能执行查询操作");
		}
		try{
			o = (T) getHibernateTemplate().get(entityClass, id);
		}catch(Exception e){
			e.printStackTrace();
			throw new DaoException("查询时发生异常，请确认操作是否正确",e);
		}
		
		if(o == null){
			throw new DaoException("当前记录无效");
		}
		return o;
	}

	
	public List<T> getAll() throws DaoException{
		List<T> list = null; 
		try{
			list = getHibernateTemplate().loadAll(entityClass);
		}catch(Exception e){
			e.printStackTrace();
			throw new DaoException("查询所有时发生异常，请确认操作是否正确",e);
		}
		return list;
	}
	
	public List<T> getAll(Class<T> entityClass) throws DaoException{
		return this.getSession().createCriteria(entityClass).list();
	}

	public long getTotalCount() throws DaoException{
		Long totalCount = 0L;
		try{
			StringBuilder sqlStr = new StringBuilder("select count(*) from ");
			sqlStr.append(entityClass.getName());
			Session session = this.getSession();
			Query query = session.createQuery(sqlStr.toString());
			totalCount = (Long) query.uniqueResult();
		}catch(Exception e){
			throw new DaoException("查询总数时发生异常，请确认操作是否正确",e);
		}
		//session.evict(totalCount);
		return totalCount;
	}

	public long getTotalCount(String wheresql)throws DaoException {
		long totalCount = 0;
		try{
			StringBuilder sqlStr = new StringBuilder("select count(*) from ");
			sqlStr.append(entityClass.getName());
			sqlStr.append(" as entity");
			if (wheresql != null && !wheresql.equals("")) {
				sqlStr.append(" where ");
				sqlStr.append(wheresql);
			}
			Session session = this.getSession();
			Query query = session.createQuery(sqlStr.toString());
			totalCount = (Long) query.uniqueResult();
		}catch(Exception e){
			throw new DaoException("查询总数时发生异常，请确认操作是否正确!",e);
		}
		return totalCount;
	}

	public long getTotalCount(HashMap params) throws DaoException{
		long totalCount = 0;
		try{
			StringBuilder sqlStr = new StringBuilder("select count(*) from ");
			sqlStr.append(entityClass.getName());
			sqlStr.append(" as entity where 1=1");
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
				if (needSet(value)) {
					sqlStr = setParameter(sqlStr, param, value);
				}
			}
			Session session = this.getSession();
			Query query = session.createQuery(sqlStr.toString());
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
				if (needSet(value)) {
					setParameterValue(query, param, value);
				}
			}
			totalCount = (Long) query.uniqueResult();
		}catch(Exception e){
			e.printStackTrace();
			throw new DaoException("查询总数时出现异常，请确认操作是否正确",e);
		}
		return totalCount;
	}
	
	public long getCroleByRoleId(HashMap paramMap){
		System.out.println(entityClass.getName());
		String p = getHibernateTemplate().find("from  Role s where s.parentid="+paramMap.get("roleid")).size()+"";
		return Long.parseLong(p);
	}
	

	public void saveOrUpdate(Object o) throws DaoException{
		if(o == null){
			throw new DaoException("保存时传入参数为空，保存失败");
		}
		try{
			getHibernateTemplate().saveOrUpdate(o);
		}catch(Exception e){
			throw new DaoException("保存失败，请确认操作是否正确",e);
		}
	}
	public void save(Object o) throws DaoException{
		if(o == null){
			throw new DaoException("保存时传入参数为空，保存失败");
		}
		try{
			getHibernateTemplate().save(o).toString();
		}catch(Exception e){
			e.printStackTrace();
			throw new DaoException("保存失败，请确认操作是否正确",e);
		}
		
	}
	public String saveObject(Object o) throws DaoException{
		if(o == null){
			throw new DaoException("保存时传入参数为空，保存失败");
		}
		try{
			return getHibernateTemplate().save(o).toString();
		}catch(Exception e){
			e.printStackTrace();
			throw new DaoException("保存失败，请确认操作是否正确",e);
		}
		
	}
	
	public void saveOrUpdateAll(Collection coll) throws DaoException{		
		if(coll == null){
			throw new DaoException("批量保存时传入参数值为空，保存失败");
		}
		try{
			getHibernateTemplate().saveOrUpdateAll(coll);
		}catch(Exception e){
			throw new DaoException("批量保存失败，请确认操作是否正确",e);
		}
	}

	public void remove(Object o) throws DaoException{
		if(o == null){
			throw new DaoException("删除时传入参数为空，删除失败");
		}
		try{
			getHibernateTemplate().delete(o);
		}catch(Exception e){
			throw new DaoException("删除失败，请确认操作是否正确",e);
		}
	}

	public void removeById(Serializable id) throws DaoException {
		remove(get(id));
	}

	public int removeByParams(String paramName, Serializable[] values) throws DaoException{
		int flag = 0;
		if (StringUtils.isEmpty(paramName) || values == null) {
			throw new DaoException("批量删除时传入参数为空，删除失败");
		} 
		try{
			Session session = this.getSession();
			Query query = null;
			StringBuilder sb = new StringBuilder();
			sb.append(" delete ");
			sb.append(entityClass.getName());
			sb.append(" as entity");
			sb.append(" where entity.");
			sb.append(paramName);
			sb.append(" in(:values) ");
			query = session.createQuery(sb.toString());
			query.setParameterList("values", values);
			flag = query.executeUpdate();
		}catch(Exception e){
			throw new DaoException("批量删除失败，请确认操作是否正确",e);
		}
		return flag;
	}

	public int removeByParams(Class clazz, String paramName,Serializable[] values) throws DaoException{
		int flag = 0;
		if (StringUtils.isEmpty(paramName) || values == null) {
			throw new DaoException("批量删除时传入参数为空，删除失败");
		}
		try {
			Session session = this.getSession();
			Query query = null;
			StringBuilder sb = new StringBuilder();
			sb.append(" delete ");
			sb.append(clazz.getName());
			sb.append(" as entity");
			sb.append(" where entity.");
			sb.append(paramName);
			sb.append(" in(:values) ");
			query = session.createQuery(sb.toString());
			query.setParameterList("values", values);
			flag = query.executeUpdate();
		} catch (Exception e) {
			throw new DaoException("批量删除失败，请确认操作是否正确",e);
		}
		return flag;
	}

	public List<T> getList(Integer start, Integer limit, String sort, String dir) throws DaoException{
		List<T> list = null;
		try {
			StringBuilder sqlStr = new StringBuilder("from ");
			sqlStr.append(entityClass.getName());
			sqlStr.append(" as entity order by entity.");
			sqlStr.append(sort);
			sqlStr.append(" ");
			sqlStr.append(dir);
			Session session = this.getSession();
			Query query = session.createQuery(sqlStr.toString());
			query.setFirstResult(start);
			query.setMaxResults(limit);
			list = query.list();
		} catch (Exception e) {
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}

	public List<T> getList(Integer start, Integer limit, String sort,String dir, String wheresql)throws DaoException {
		List<T> list = null;
		try {
			StringBuilder sqlStr = new StringBuilder("from ");
			sqlStr.append(entityClass.getName());
			sqlStr.append(" as entity");
			if (StringUtils.isNotEmpty(wheresql)) {
				sqlStr.append(" where ");
				sqlStr.append(wheresql);
			}
			sqlStr.append(" order by entity.");
			sqlStr.append(sort);
			sqlStr.append(" ");
			sqlStr.append(dir);
			Session session = this.getSession();
			Query query = session.createQuery(sqlStr.toString());
			query.setFirstResult(start);
			query.setMaxResults(limit);
			list = query.list();
		} catch (Exception e) {
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}
	
	public List<T> getList(String wheresql)throws DaoException{
		List<T> list = null;
		try {
			StringBuilder sqlStr = new StringBuilder("from ");
			sqlStr.append(entityClass.getName());
			sqlStr.append(" as entity");
			if (wheresql != null && !wheresql.equals("")) {
				sqlStr.append(" where ");
				sqlStr.append(wheresql);
			}
			Session session = this.getSession();
			Query query = session.createQuery(sqlStr.toString());
			list = query.list();
		} catch (Exception e) {
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}

	public List<T> findByHql(String hql, Object... values) throws DaoException {
		List<T> list = null;
		try{
		if (values.length == 0)
			list = getHibernateTemplate().find(hql);
		else
			list = getHibernateTemplate().find(hql, values);
		}catch(Exception e){
			e.printStackTrace();
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}


	public List<T> findByProperty(String name, Object... value)  throws DaoException {
		List<T> list = null;
		try{
			Assert.hasText(name);
			Criteria criteria = getEntityCriteria();
			if (value instanceof Object[]) {
				criteria.add(Restrictions.in(name, (Object[]) value));
			} else {
				criteria.add(Restrictions.eq(name, value));
			}
			list = criteria.list();
		}catch(Exception e){
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}

	public List<T> findByProperties(String[] names, Object[] values) throws DaoException{
		List<T> list = null;
		try{
			Criteria criteria = getEntityCriteria();
			for (int i = 0; i < names.length; i++) {
				criteria.add(Restrictions.eq(names[i], values[i]));
			}
			list = criteria.list();
		}catch(Exception e){
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}

	public T findUniqueByProperty(String name, Object value) throws DaoException{
		T t = null;
		try{
			Assert.hasText(name);
			Criteria criteria = getEntityCriteria();
			criteria.add(Restrictions.eq(name, value));
			t = (T)criteria.uniqueResult();
		}catch(Exception e){
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return t;
	}

	
	public List<T> findByProperties(HashMap params)throws DaoException{
		return findByProperties(params, "","");
	}
	
	public List<T> findByProperties(HashMap params,String sort,String dir)throws DaoException{
		List<T> list = null;
		try{
			StringBuilder sqlStr = new StringBuilder("from ");
			sqlStr.append(getEntityClass().getName());
			sqlStr.append(" as entity ");
			sqlStr.append("where 1=1");
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
				if (needSet(value)) {
					sqlStr = setParameter(sqlStr, param, value);
				}
			}
			if(StringUtils.isNotEmpty(sort)){
				sqlStr.append(" order by entity.");
				sqlStr.append(sort);
				sqlStr.append(" ");
				sqlStr.append(dir);
			}
			
			Session session = this.getSession();
			Query query = session.createQuery(sqlStr.toString());
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
				if (needSet(value)) {
					setParameterValue(query, param, value);
				}
			}
			list = query.list();
		}catch(Exception e){
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}
	public List<T> findByProperties(long start, long limit, String sort,String dir, HashMap params) throws DaoException{
		List<T> list = null;
		try{
		StringBuilder sqlStr = new StringBuilder("from ");
		sqlStr.append(entityClass.getName());
		sqlStr.append(" as entity ");
		sqlStr.append("where 1=1 ");
		for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
			String param = (String) itr.next();
			Object value = params.get(param);
			if (needSet(value)) {
				sqlStr = setParameter(sqlStr, param, value);
			}
		}
		if(StringUtils.isNotEmpty(sort)){
			sqlStr.append(" order by entity.");
			sqlStr.append(sort);
			sqlStr.append(" ");
			sqlStr.append(dir);
		}
		Session session = this.getSession();
		
		Query query = session.createQuery(sqlStr.toString());
		if(limit != 0){
			query.setFirstResult((int)start);
			query.setMaxResults((int)limit);
		}
		
		for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
			String param = (String) itr.next();
			Object value = params.get(param);
			if (needSet(value)) {
				setParameterValue(query, param, value);
			}
		}
		list = query.list();
		}catch(Exception e){
			e.printStackTrace();
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}
	/**
	 * 根据分页bean来查询数据的方法
	 * @param pageBean 分页bean
	 * @return  根据分页条件查询出来的数据
	 * @throws DaoException
	 * @author anlu
	 * @date  2012.2.27
	 */
	public List<T> findByPageBeanProperties(PagingBean pageBean, HashMap params) throws DaoException{
		return findByProperties(pageBean.getStart(), pageBean.getLimit(), pageBean.getSort(), pageBean.getDir(), params);
//		List<T> list = null;
//		try{
//		StringBuilder sqlStr = new StringBuilder("from ");
//		sqlStr.append(entityClass.getName());
//		sqlStr.append(" as entity ");
//		sqlStr.append("where 1=1");
//		for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
//			String param = (String) itr.next();
//			Object value = params.get(param);
//			if (needSet(value)) {
//				sqlStr = setParameter(sqlStr, param, value);
//			}
//		}
//		if(StringUtils.isNotEmpty(pageBean.getSort())){
//			sqlStr.append(" order by entity.");
//			sqlStr.append(pageBean.getSort());
//			sqlStr.append(" ");
//			sqlStr.append(pageBean.getDir());
//		}
//		Session session = this.getSession();
//		
//		Query query = session.createQuery(sqlStr.toString());
//		if(pageBean.getLimit() != 0){
//			query.setFirstResult((int)pageBean.getStart());
//			query.setMaxResults((int)pageBean.getLimit());
//		}
//		
//		for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
//			String param = (String) itr.next();
//			Object value = params.get(param);
//			if (needSet(value)) {
//				setParameterValue(query, param, value);
//			}
//		}
//		list = query.list();
//		}catch(Exception e){
//			e.printStackTrace();
//			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
//		}
//		return list;
	}

	private boolean needSet(Object value) throws DaoException{
		boolean needSet = false;
		if (value != null) {
			if (value instanceof Collection) {
				if (!((Collection) value).isEmpty()) {
					needSet = true;
				}
			} else if (value instanceof Object[]) {
				if (((Object[]) value).length > 0) {
					needSet = true;
				}
			} else if (value instanceof ParamBean) {
				if (((ParamBean) value).getValue() != null) {
					needSet = true;
				}
			} else if (value instanceof String) {
				if (!((String) value).trim().equals("")) {
					needSet = true;
				}
			} else {
				needSet = true;
			}
		}
		return needSet;
	}

	private StringBuilder setParameter(StringBuilder sqlStr, String fieldName,
			Object fieldValue) throws DaoException{
		sqlStr.append(" and entity.");
		if (fieldValue instanceof ParamBean) {
			sqlStr.append(((ParamBean) fieldValue).getParam());
			sqlStr.append(" ");
			sqlStr.append(((ParamBean) fieldValue).getExpression());
			Object paramterValue = ((ParamBean) fieldValue).getValue();
			if(paramterValue instanceof Collection || paramterValue instanceof Object[]){
				sqlStr.append(" in (:");
				sqlStr.append(fieldName);
				sqlStr.append(")");
			}else{
				sqlStr.append(" :");
				sqlStr.append(fieldName);
			}
		} else {
			sqlStr.append(fieldName);
			fieldName = fieldName.replace(".", "_");
			if (fieldValue instanceof Collection
					|| fieldValue instanceof Object[]) {
				sqlStr.append(" in (:");
				sqlStr.append(fieldName);
				sqlStr.append(")");
			} else {
				sqlStr.append(" = :");
				sqlStr.append(fieldName);
			}
		}
		return sqlStr;
	}

	private Query setParameterValue(Query query, String paramName,
			Object fieldValue) throws DaoException{
		paramName = paramName.replace(".", "_");
		if (fieldValue instanceof Collection) {
			query.setParameterList(paramName, (Collection) fieldValue);
		} else if (fieldValue instanceof Object[]) {
			query.setParameterList(paramName, (Object[]) fieldValue);
		} else if (fieldValue instanceof ParamBean) {
			Object paramterValue = ((ParamBean) fieldValue).getValue();
			if(paramterValue instanceof Collection || paramterValue instanceof Object[]){
				if(paramterValue instanceof Collection){
					query.setParameterList(paramName, (Collection) paramterValue);
				}else if(paramterValue instanceof Object[]){
					query.setParameterList(paramName, (Object[]) paramterValue);
				}
			}else{
				query.setParameter(paramName, ((ParamBean) fieldValue).getValue());
			}			
		} else {
			query.setParameter(paramName, fieldValue);
		}
		return query;
	}


	/**
	 * 取得Entity的Criteria.
	 */
	private Criteria getEntityCriteria() throws DaoException{
		return getSession().createCriteria(entityClass);
	}
	
	public List findBySQL(String sql)throws DaoException{//谭冬梅备注
		List list = null;
		try{
		Session session = this.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		list = query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
		session.flush();
		session.clear();
		this.releaseSession(session);
		}catch(Exception e){
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}

		return list;
	}
	public List getBySQL(String sql)throws DaoException{//谭冬梅备注
		List list = null;
		try{
		Session session = this.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		list = query.list();
		session.flush();
		session.clear();
		this.releaseSession(session);
		}catch(Exception e){
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}

		return list;
	}
	/**
	 * 根据sql语句查询数据,返回list集合中生成map
	 */
	public List findByPropertiesBySQL(long start, long limit,
			String sort, String dir, HashMap params, String sql, Class entity,
			boolean flagPage, String groupby) throws DaoException {
		List<T> list = null;
		try{
			StringBuilder sqlStr = new StringBuilder(sql);
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
				if (needSet(value)) {
					sqlStr = setParameterSQL(sqlStr, param, value);
				}
			}
			sqlStr.append(groupby);
			if(StringUtils.isNotEmpty(sort) && StringUtils.isNotEmpty(dir)){
				sqlStr.append(" order by ");
				sqlStr.append(sort);
				sqlStr.append(" ");
				sqlStr.append(dir);
			}
			
			Session session = this.getSession();
			SQLQuery query = session.createSQLQuery(sqlStr.toString());
			if(entity != null){
				query.addEntity("entity",entity);
			}
			if(flagPage){
				query.setFirstResult((int)start);
				query.setMaxResults((int)limit);
			}
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
				if (needSet(value)){
					setParameterValue(query, param, value);
				}
			}
			list = query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
			session.flush();
			session.clear();
			this.releaseSession(session);
		}catch(Exception e){
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}
	/**
	 * 根据sql语句查询数据,返回list集合中生成map，params中的值为空也当条件查询
	 */
	public List findByPropertiesBySQLWithNullParam(long start, long limit,
			String sort, String dir, HashMap params, String sql, Class entity,
			boolean flagPage, String groupby) throws DaoException {
		List<T> list = null;
		try{
			StringBuilder sqlStr = new StringBuilder(sql);
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
//				if (needSet(value)) {
					sqlStr = setParameterSQL(sqlStr, param, value);
//				}
			}
			sqlStr.append(groupby);
			if(StringUtils.isNotEmpty(sort) && StringUtils.isNotEmpty(dir)){
				sqlStr.append(" order by ");
				sqlStr.append(sort);
				sqlStr.append(" ");
				sqlStr.append(dir);
			}
			
			Session session = this.getSession();
			SQLQuery query = session.createSQLQuery(sqlStr.toString());
			if(entity != null){
				query.addEntity("entity",entity);
			}
			if(flagPage){
				query.setFirstResult((int)start);
				query.setMaxResults((int)limit);
			}
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
//				if (needSet(value)){
					setParameterValue(query, param, value);
//				}
			}
			list = query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
			session.flush();
			session.clear();
			this.releaseSession(session);
		}catch(Exception e){
			throw new DaoException("查询时出现异常，请确认操作是否正确",e);
		}
		return list;
	}
	
	private StringBuilder setParameterSQL(StringBuilder sqlStr, String fieldName,
			Object fieldValue) {
		sqlStr.append(" and ");
		if (fieldValue instanceof ParamBean) {
			sqlStr.append(((ParamBean) fieldValue).getParam());
			sqlStr.append(" ");
			sqlStr.append(((ParamBean) fieldValue).getExpression());
			sqlStr.append(" :");
			sqlStr.append(fieldName);
		} else {
			sqlStr.append(fieldName);
			fieldName = fieldName.replace(".", "_");
			if (fieldValue instanceof Collection
					|| fieldValue instanceof Object[]) {
				sqlStr.append(" in (:");
				sqlStr.append(fieldName);
				sqlStr.append(")");
			} else {
				sqlStr.append(" = :");
				sqlStr.append(fieldName);
			}
		}
		return sqlStr;
	}
	public long getTotalCountBySQL(HashMap params, String sql, String groupby) throws DaoException{
		long totalCount = 0;
		try{
			
			StringBuilder sqlStr = new StringBuilder(sql);
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
				if (needSet(value)) {
					sqlStr = setParameterSQL(sqlStr, param, value);
				}
			}
			sqlStr.append(groupby);
			Session session = this.getSession();
			SQLQuery query = session.createSQLQuery(sqlStr.toString());
			for (Iterator itr = params.keySet().iterator(); itr.hasNext();) {
				String param = (String) itr.next();
				Object value = params.get(param);
				if (needSet(value)) {
					setParameterValue(query, param, value);
				}
			}
			if(StringUtils.isNotEmpty(groupby)){
				int size = query.list().size();
				session.flush();
				session.clear();
				this.releaseSession(session);
				return size;
			}
			Object o = query.uniqueResult();
			totalCount = Long.parseLong(o.toString());
			session.flush();
			session.clear();
			this.releaseSession(session);
		}catch(Exception e){
			throw new DaoException("查询总数时出现异常，请确认操作是否正确",e);
		}
		return totalCount;
	}
	
	public int executeSQL(final String sqlStr) throws DaoException {
		Integer returnInt = (Integer) this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						SQLQuery query = session.createSQLQuery(sqlStr);
						int number = query.executeUpdate();
						return new Integer(number);
					}
				});
		return returnInt.intValue();
	}
	
	public Long executeSeq(final String seqName) throws DaoException {
		Number seqValue = (Number) this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						SQLQuery query = session.createSQLQuery("select "
								+ seqName
								+ ".nextval as SEQUENCEVALUE from dual");
						
						query.addScalar("SEQUENCEVALUE", new LongType());
						return (Number) query.uniqueResult();
					}
				});
		return seqValue.longValue();
	}
	
	/**
	 * 根据HQL语句进行分页查询
	 * @param hql HQL语句
	 * @param params HQL语句带的多个参数值
	 * @param offset 从第几条记录开始查询
	 * @param pagesize 每页显示多少行
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public PageResult searchPaginated(String hql, Map filterMap){
		int page = (Integer) filterMap.get("page");
		int rows = (Integer) filterMap.get("rows");
		int start = rows * ( page - 1); //开始的记录
		//获取记录总数
		String countHql = getCountQuery(hql);
		Query query = getSession().createQuery(countHql);
		int total =((Number) query.uniqueResult()).intValue();
		
		//获取当前页的结果集
		query = getSession().createQuery(hql);
		
		query.setFirstResult(start);
		query.setMaxResults(rows);
		List<?> datas = query.list();
		
		PageResult pm = new PageResult();
		pm.setRowCount(total);
		pm.setData(datas);
		return pm;
	}
	
	/**
	 * 根据HQL语句，获得查找总记录数的HQL语句
	 * 如：
	 * select ... from Orgnization o where o.parent is null
	 * 经过转换，可以得到：
	 * select count(*) from Orgnization o where o.parent is null
	 * @param hql
	 * @return
	 */
	private String getCountQuery(String hql){
		int index = hql.indexOf("from");
		if(index != -1){
				return "select count(*) " + hql.substring(index);
		}
		throw new SystemException("无效的HQL查询语句");
	}
	

	/**
	 * HQL 主要应用多表查询时的HQL查询
	 * PageResult
	 * 2011-5-11上午09:18:11
	 * author：李数
	 */
	@SuppressWarnings("unchecked")
	public PageResult searchPaginated(String counthql,String queryhql, Map filterMap){
		int page = (Integer) filterMap.get("page");
		int rows = (Integer) filterMap.get("rows");
		int start = rows * ( page - 1); //开始的记录
		//获取记录总数 
		Query query = getSession().createQuery(counthql);		
		int total =((Number) query.uniqueResult()).intValue();
		
		//获取当前页的结果集
		query = getSession().createQuery(queryhql);
		
		query.setFirstResult(start);
		query.setMaxResults(rows);
		List<?> datas = query.list();
		
		PageResult pm = new PageResult();
		pm.setRowCount(total);
		pm.setData(datas);
		return pm;
	}
	
	/**
	 * 根据纯脆的SQL语句来查询
	 * PageResult
	 * 2011-5-11上午09:19:38
	 * author：李数
	 */
	public PageResult searchBySQL(String countsql,String querysql, Map filterMap){
		int page = (Integer) filterMap.get("page");
		int rows = (Integer) filterMap.get("rows");
		int start = rows * ( page - 1); //开始的记录
		//获取记录总数 
		Query query = getSession().createSQLQuery(countsql);
		List<Number> count = query.list();
		int total = Integer.parseInt(count.get(0).toString());
		
		//获取当前页的结果集
		query = getSession().createSQLQuery(querysql);
		query.setFirstResult(start);
		query.setMaxResults(rows);
		List<?> datas = query.list();
		PageResult pm = new PageResult();  
		pm.setRowCount(total);
		pm.setData(datas);
		return pm; 
	}
	
	public void merge(Object o) throws DaoException {
		try{
			this.getHibernateTemplate().merge(o);
		}catch(Exception e){
			throw new DaoException("merge时出错，请确认操作是否正确",e);
		}
	}

	

	public List executeHql(String hql) throws DaoException {
		return this.getHibernateTemplate().find(hql);
	}
	
	
	
}
